﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using IToursDataService;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using IToursDataService.Models;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“RestaurantOrderCtrl”。
    public class RestaurantOrderCtrl : IRestaurantOrderCtrl
    {
        public DataTable GetRestaurantOrderData(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT r.*,p.City FROM ord_RestaurantOrder r,bas_Provider p WHERE r.ProviderID=p.ProviderID AND TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public DataTable GetRestaurantOrderData(string tourGroupId, string localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            StringBuilder formatSql = new StringBuilder();

            //根据供应商为签约供应商，和制定团号的签单娱乐信息
            formatSql.AppendFormat(@"SELECT * FROM ord_RestaurantOrder WHERE  TourGroupID='{0}' AND LocalPlanScheduleID={1} ORDER BY ServiceDate asc,ServiceName desc",
                                        tourGroupId, localPlanScheduleId);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public DataTable GetRestaurantInfoForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("SELECT distinct bas_Provider.[ProviderName], bas_Provider.[Contact], bas_Provider.[Telephone], bas_Provider.[fax]  FROM bas_Provider,ord_RestaurantOrder WHERE ord_RestaurantOrder.TourgroupID='{0}' and bas_Provider.ProviderID=ord_RestaurantOrder.ProviderID", tourGroupID);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }
        public int UpdateRestaurantOrderData(RestaurantOrderEntity restaurantOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlOrder = string.Format(@"UPDATE ord_RestaurantOrder SET [ServiceDate]='{0}', [ProviderID]={1}, [ProviderName]='{2}',[ServiceName]='{3}'   
                                                                                WHERE RestaurantOrderID={4}",
                                                                                restaurantOrder.ServiceDate,
                                                                                restaurantOrder.ProviderID,
                                                                                restaurantOrder.ProviderName,
                                                                                restaurantOrder.ServiceName,
                                                                                restaurantOrder.RestaurantOrderID
                                                                                );

            return db.ExecuteNonQuery(CommandType.Text, sqlOrder);
        }

        public int CreateRestaurantOrderData(RestaurantOrderEntity restaurantOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlOrder = string.Format(@"INSERT INTO ord_RestaurantOrder([TourgroupID], [TourgroupName], [ProviderID], [ProviderName], 
                                                                              [LocalPlanScheduleID], [ServiceDate],[ServiceName],[Remark])
                                                                              VALUES('{0}','{1}',{2},'{3}',{4},'{5}','{6}','{7}');Select SCOPE_IDENTITY();",
                                                                                   restaurantOrder.TourgroupID,
                                                                                   restaurantOrder.TourgroupName,
                                                                                   restaurantOrder.ProviderID,
                                                                                   restaurantOrder.ProviderName,
                                                                                   restaurantOrder.LocalPlanScheduleID,
                                                                                   restaurantOrder.ServiceDate,
                                                                                   restaurantOrder.ServiceName,
                                                                                   restaurantOrder.Remark);
            int orderid = int.Parse(db.ExecuteScalar(CommandType.Text, sqlOrder).ToString());
            return orderid;
        }
        public int DeleteRestaurantOrder(string restaurantOrderId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM ord_RestaurantOrder WHERE RestaurantOrderID={0}", restaurantOrderId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
    }
}
